'''
now
    WEBAPI: getIsPassFromQCDCS(self, serial='23059B2B76')
'''


# try:
#     from .sqlserver_client import sql_server_client
# except:
#     from sql_server.sqlserver_client import sql_server_client
import random
import traceback
import requests

try:
    # from .sqlserver_client_sqlalchemy import sql_server_client
    from .ly1_factory import Lay1SqlServerClientFactory
except:
    # from sql_server.sqlserver_client_sqlalchemy import sql_server_client
    from sql_server.ly1_factory import Lay1SqlServerClientFactory


# for inserting
# from sql_server.tables import Kitting_Product_Table, Kitting_ProductKeyMaterial_Table
from datetime import datetime
from log import logger

# for inserting
from sql_server.tables import Kitting_Product_Table, Kitting_ProductKeyMaterial_Table
from datetime import datetime
from log import logger

# mssql_config_LineKitting = {
#     'server': 'ch71w0120',
#     'user': 'MdcsSzPr-rw',
#     'password': 'X7tu5#99RT1',
#     'database': 'LineKitting'
# }
# mssql_config_MDCSPr = {
#     'server': 'ch3uw3076',
#     'user': 'MdcsSzPr-rw',
#     'password': 'X7tu5#99RT1',
#     'database': 'MDCSPr'
# }

# mess_config


class HuoniMessClient:

    def __init__(self, driver = 'sqlalchemy'):
        mssql_config1 = {
            'server': 'ch71w0120',
            'user': 'MdcsSzPr-rw',
            'password': 'X7tu5#99RT1',
            'database': 'LineKitting'
        }
        mssql_config2 = { # for tblItems tables
            'server': 'CH3UW3076',
            'user': 'MdcsSzPr-rw',
            'password': 'X7tu5#99RT1',
            'database': 'MDCSPr'
        }
        self.method = 'WEBAPI'
        # self.method = 'SQL'

        # self.sqlclient = sql_server_client(driver, mssql_config1['server'], mssql_config1['user'],
        #                                    mssql_config1['password'], mssql_config1['database'])
        # self.sqlclient_MDCSPr = sql_server_client(mssql_config2['server'], mssql_config2['user'],
        #                                    mssql_config2['password'], mssql_config2['database'])
        self.sqlclient = Lay1SqlServerClientFactory.create(driver, mssql_config1['server'], mssql_config1['user'],
                                           mssql_config1['password'], mssql_config1['database'])

        self.sqlclient_MDCSPr = Lay1SqlServerClientFactory.create(driver, mssql_config2['server'], mssql_config2['user'],
                                           mssql_config2['password'], mssql_config2['database'])
    def is_connect(self):
        try:
            # self.sqlclient.connect()
            # self.sqlclient.close()
            self.getBzBySku('123')
            return True
        except:
            logger.error(traceback.format_exc())
            return False

    def getBzBySku(self, sku=''): # 获取包装信息
        sql_query_str = f"SELECT * FROM dbo.Kitting_newunittype WHERE F_SKU='{sku}'"
        rows = self.sqlclient.select(sql_query_str)
        return rows # [{},{}]
    def getBzByUnitType(self, unit_type=''): # 获取包装信息 20250212
        sql_query_str = f"SELECT * FROM dbo.Kitting_newunittype WHERE F_Unit_type='{unit_type}'"
        rows = self.sqlclient.select(sql_query_str)
        return rows # [{},{}]

    def getKitUserByName(self, username=''): # 获取 USER
        sql_query_str = f"SELECT * FROM Kitting_User WHERE F_UserName='{username}'"
        rows = self.sqlclient.select(sql_query_str)
        return rows # [{},{}]


    def getFAIByUnitType(self, unit_type='EDA52-00AE61N21RK'): # 获取 FAI
        # self.sqlclient.connect(database='IQCDCSForTesting')
        sql_query_str =f"select f_items, f_desc as Items, f_needFai as FAIStatus, F_KeyPart as KeyPartNeedScanSN from Kitting_FAIContains where F_Unit_typeID = (select f_id from kitting_newunittype where f_unit_type='{unit_type}') collate database_default"
        rows = self.sqlclient.select(sql_query_str) # return [{},{}]
        # res = {
        #     'FAIStatus':rows[0]['FAIStatus'], # =1表示需要做FAI检查，输入并比较确认具体的物料的料号是否正确
        #     'KeyPartNeedScanSN': rows[0]['KeyPartNeedScanSN'],  # =0 表示不需要输入具体的SN
        #     'Items': rows[0]['Items'], # desc
        #     'f_items': rows[0]['f_items'], #  like pn
        # }

        return rows # {}


    def getItemsUpload(self, unit_type='EDA52-00AE61N21RK'): # 获取 which to upload
        # self.sqlclient.connect(database='IQCDCSForTesting')
        sql_query_str =f"select F_Items, F_Desc from Kitting_FAIContains where F_KeyPart='true' and f_unit_typeid=(select f_id from Kitting_NewUnitType where f_unit_type='{unit_type}')"
        rows = self.sqlclient.select(sql_query_str) # return [{},{}]
        return rows # [{},{}]

    def getIsPassFromQCDCS(self, serial='23059B2B76'): # 获取, qcdcs web inference??
        rows = []
        if self.method == 'SQL':
            sql_query_str =f"select model,BF_Model, Fa_Status, Pre_Status , Post_Status from tblItems where serial='{serial}'"
            rows = self.sqlclient_MDCSPr.select(sql_query_str) # return [{},{}]
        elif self.method == 'WEBAPI':
            try:
                # 发送GET请求
                url = "https://appportal.honeywell.com/ApiSystem/MDCS/ProductInfo/GetProductStatusInfo"
                params = {
                    'Serial': serial,  # 需要替换为实际的Serial值
                    'Id_Department': 'e8d87566-4e1f-4ebf-b6aa-3220f4d26e54',
                    # 'Token': '97515b77-58aa-4768-9505-9d2086b11493'
                }
                headers = {
                    'token': '97515b77-58aa-4768-9505-9d2086b11493'
                    # 'Authorization': 'Bearer 97515b77-58aa-4768-9505-9d2086b11493'
                }

                response = requests.get(url, params=params, headers=headers, verify=False) # disable ssl verify
                # print(response.status_code)  # 打印响应状态码
                # print(response.text)  # 打印响应正文
                # print(response.json())  # 如果响应内容是JSON格式，可以使用json()方法解析
                data_dict = response.json()['Data']
                if data_dict:
                    data_dict['model'] = data_dict['ProductModel']  # jianrong sql
                    # data_dict['']
                rows = [data_dict]
            except:
                logger.error('MES WEBAPI 通讯异常')
                logger.info(traceback.format_exc())
                rows = []
        return rows # [{},{}]


    def getLabelName(self, F_Unit_type='EDA52-00AE61N21RK', F_LabelGroupName='KitLabel'): # 获取, Kitting_LabelTemplete
        try:
            sql_query_str =f"select F_LabelName from Kitting_LabelTemplete where f_id in (select F_LabelID from Kitting_UnitTypePrinter where F_UnitTypeID=(select f_id from kitting_newunittype where F_Unit_type='{F_Unit_type}')) and F_LabelGroupID=(select f_id from Kitting_LabelGroup where F_LabelGroupName='{F_LabelGroupName}') "
            rows = self.sqlclient.select(sql_query_str) # return [{},{}]
        except:
            logger.error('打印标签名查询失败')
            logger.info(traceback.format_exc())
        return rows # [{},{}]

    def getLabelContent(self, F_LabelName='50175378-001_D.POF', F_Unit_type='EDA52-00AE61N21RK'): # 获取, Kitting_LabelTemplete

        sql_query_str =f"select F_MDCSDeviceName,F_MDCSVariable,F_MDCSVariableName,F_VariableName,F_VariableValue from Kitting_LabelVariable where F_LabelID=(select f_id from Kitting_LabelTemplete where F_LabelName='{F_LabelName}' ) and F_UnitTypeID=(select f_id from Kitting_NewUnitType where F_Unit_type= '{F_Unit_type}') collate database_default"
        rows = self.sqlclient.select(sql_query_str) # return [{},{}]

        return rows # [{},{}]

    def getLabelVarFromQCDCS(self, pn='17117B0EFE', sn='', var_name = ''): # 获取 value to print, qcdcs
        rows = []
        if self.method == 'SQL':
            sql_query_str = f"select top(100)* from BT_WifiAddressRecord where partnumber='{pn}' and SN='{sn}'"
            rows = self.sqlclient_MDCSPr.select(sql_query_str) # return [{},{}]

        # elif self.method == 'WEBAPI':
        #
        #     # 发送GET请求
        #     url = "https://appportal.honeywell.com/ApiSystem/MDCS/ProductInfo/GetMDCSVariableByLineKitting?VarName=IMEI"
        #     params = {
        #         'Key': sn,  # 需要替换为实际的Serial值
        #         'Id_Department': 'e8d87566-4e1f-4ebf-b6aa-3220f4d26e54',
        #         # 'Token': '97515b77-58aa-4768-9505-9d2086b11493'
        #     }
        #     headers = {
        #         'token': '97515b77-58aa-4768-9505-9d2086b11493'
        #         # 'Authorization': 'Bearer 97515b77-58aa-4768-9505-9d2086b11493'
        #     }
        #
        #     response = requests.get(url, params=params, headers=headers, verify=False)  # disable ssl verify
        #
        #     data_dict = response.json()['Data']
        #     # if data_dict:
        #     #     data_dict['model'] = data_dict['ProductModel']  # jianrong sql
        #     print(data_dict)
        #     # rows = [data_dict]
        #     rows = []

        return rows # [{},{}]

    def getLabelVarFromQCDCSAndName(self, sn='', var_name = ''): # 获取 value to print, qcdcs
        data_str = ''
        if self.method == 'WEBAPI':
            try:
                # 发送GET请求
                url = "https://appportal.honeywell.com/ApiSystem/MDCS/ProductInfo/GetMDCSVariableByLineKitting"
                params = {
                    'Key': sn,  # 需要替换为实际的Serial值
                    'VarName': var_name,
                    'Id_Department': 'e8d87566-4e1f-4ebf-b6aa-3220f4d26e54',
                    # 'Token': '97515b77-58aa-4768-9505-9d2086b11493'
                }
                headers = {
                    'token': '97515b77-58aa-4768-9505-9d2086b11493'
                    # 'Authorization': 'Bearer 97515b77-58aa-4768-9505-9d2086b11493'
                }

                response = requests.get(url, params=params, headers=headers, verify=False)  # disable ssl verify
                # print(response)
                data_str = response.json()['Data']
                # if data_dict:
                #     data_dict['model'] = data_dict['ProductModel']  # jianrong sql
                logger.info(f'WEB {sn} {var_name} {data_str} ') #
            except:
                logger.error('WEBAPI 通讯异常')
                logger.info(traceback.format_exc())
                data_str = ''
            # rows = []

        return data_str # [{},{}]

    # def upload(self, data): # 上传
    #
    #     sql_insert_format_str = \
    #         ("INSERT INTO dbo.Kitting_User "
    #          "([F_UserName],[F_Password],[SecurityRight]) "
    #          "VALUES (%s, %s, %s)")  # 修改它
    #     # data = [
    #     #     {
    #     #     'F_UserName': 'user1',
    #     #     'F_Password': '123',
    #     #     'SecurityRight': 'xx1',
    #     # },
    #     #     {
    #     #     'F_UserName': 'user2',
    #     #     'F_Password': '12345',
    #     #     'SecurityRight': 'xx2',
    #     # }
    #     # ]
    #
    #     data_tuple = [tuple(d.values()) for d in data]
    #
    #     self.sqlclient.insert(sql_insert_format_str, data_tuple)
    #     pass


    def get_packing_record(self, sn='23257B29E2'):
        sql_query_str =f"select * from Kitting_Product where sn='{sn}'"
        rows = self.sqlclient.select(sql_query_str) # return [{},{}]
        return rows # [{},{}]


    def upload_packing_record(self, sn='23257B29E2', unit_type='', eid='AUTO_A'):
        '''
        上传包装记录
        :param sn:
        :param unit_type:
        :return: True or False 上传成功 or 失败
        '''
        msg = ''
        rows = self.get_packing_record(sn)
        if len(rows) > 0:
            logger.warning(f'包装记录 {sn} 已经存在，不再上传')
            msg = f'包装记录 {sn} 已经存在，不再上传'
            return False, msg

        # insert
        # sn = '23257B29E2'
        ctime = datetime.now()
        boxnumber = ctime.strftime('%Y%m%d%H%M%S') + str(random.randint(10,99))
        kp_record = self.sqlclient.create_inserting_record(
            sn=sn, unit_type=unit_type,
            boxnumber=boxnumber, date=ctime,
            OrderNo='', EID=eid
        )
        # kp_record = Kitting_Product_Table(
        #     sn=sn, unit_type=unit_type,
        #     boxnumber=boxnumber, date=ctime,
        #     OrderNo='', EID='GLK')
        # print(f'insert {kp_record.__dict__}')
        is_ok = self.sqlclient.insert(kp_record)
        if not is_ok:
            msg = '上传失败，已回滚，请重新上传'
        return is_ok, msg

    def close(self):
        self.sqlclient.close()


class SimHuoniMessClient:
    def __init__(self):
        pass
    def getBzBySku(self, sku=''): # 获取包装信息
        return []  # [{},{}]
    def getKitUserByName(self, username=''): # 获取 USER
        return []  # [{},{}]

    def getFAIByUnitType(self, unit_type='EDA52-00AE61N21RK'): # 获取 FAI
        return []  # [{},{}]

    def getItemsUpload(self, unit_type='EDA52-00AE61N21RK'): # 获取 which to upload
        # self.sqlclient.connect(database='IQCDCSForTesting')
        return []  # [{},{}]
    def getIsPassFromQCDCS(self, serial='17117B0EFE'): # 获取, qcdcs web inference??
        return []  # [{},{}]
    def getLabelName(self, F_Unit_type='EDA52-00AE61N21RK', F_LabelGroupName='KitLabel'): # 获取, Kitting_LabelTemplete
        return []  # [{},{}]
    def getLabelContent(self, F_LabelName='50175378-001_D.POF', F_Unit_type='EDA52-00AE61N21RK'): # 获取, Kitting_LabelTemplete
        return []  # [{},{}]
    def getLabelVarFromQCDCS(self, pn='17117B0EFE', sn=''): # 获取 value to print, qcdcs
        return [] # [{},{}]
    def close(self):
        pass
def test_HuoniMessClient():

    # hm = HuoniMessClient(mssql_config['server'],mssql_config['user'],
    #                      mssql_config['password'],mssql_config['database'],)
    hm = HuoniMessClient()

    for i in range(1):
        #'CT47-X1N-5ED1E0G' 'CT47-X1N-37D1E0G'
        res = hm.getBzBySku(sku='CT47-X1N-37D1E0G') # 拉
        print(f'1 product information...')
        for k,v in res[0].items():
            print(f'{k}: {v}')
        # print(res)
    res_ispass = hm.getIsPassFromQCDCS()
    print(res_ispass)


    hm.close()


def test_upload_packing_record():
    hm = HuoniMessClient()

    sn = '23059B2B78'
    unit_type = 'CT45-L1N-28D120G'
    hm.upload_packing_record(sn, unit_type)

def test_getIsPassFromQCDCS():
    mess_handle = HuoniMessClient()
    mess_handle.method = 'WEBAPI'
    # serial_number = '24180B2E99'
    serial_number = '24186B0C8E'
    # res_isPass = mess_handle.getIsPassFromQCDCS(serial=serial_number)
    # isPass = (len(serial_number) == 10 and len(res_isPass) > 0
    #                       and res_isPass[0]['model'] == 'CT45-L1N-28D120G'
    #                       and (res_isPass[0]['Pre_Status']==2 and res_isPass[0]['Fa_Status']==2 and res_isPass[0]['Post_Status']==2 )) # 策略
    # print(res_isPass)
    # print(isPass)

    # res_isPass = mess_handle.getLabelVarFromQCDCS(pn='CT45P-L1N-38D1E0G', sn='24186B0C8E')
    res_isPass = mess_handle.getLabelVarFromQCDCSAndName(sn='24186B0C8E',var_name='IMEI')

    print(res_isPass)


if __name__ =='__main__':
    # test_HuoniMessClient()
    # work_thread()
    # test_upload_packing_record()
    test_getIsPassFromQCDCS()